package com.hxx;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Properties;
import java.sql.ResultSet;



public class DbUtils {

    /**
     * 注册驱动
     * 获取数据库连接
     */
    public Connection getConnection()  {
        Connection connection = null;
        Properties properties = new Properties();
        try {
            //读取配置文件
            properties.load(Thread.currentThread().getContextClassLoader()
                    .getResourceAsStream("jdbc.properties"));
            //注册驱动
            Class.forName(properties.getProperty("driver"));
            //获取数据库连接
            String url = properties.getProperty("url");
            String user = properties.getProperty("user");
            String password = properties.getProperty("password");
            connection = DriverManager.getConnection(url,
                            user,
                            password);
        }catch (Exception e){
            e.printStackTrace();
        }
        return connection;
    }

    /**
     * 关闭链接
     */
    /**
     * 关闭链接
     */
    public void close(Connection connection) {
        if (connection != null) {
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
    /**
     * 查询
     */
    public void query(String username) {
        //获取数据库连接
        Connection connection = getConnection();
        //查询
        String sql1 = "select * from t_user where username = ?";
        String sql = "select * from t_user where username like ?";
        //执行查询
        try {
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, username + "%");
            //执行查询返回结果集
            ResultSet resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                String username1 = resultSet.getString("username");
                String sex = resultSet.getString("sex");
                int age = resultSet.getInt("age");
                System.out.println(username1 + " " + sex + " " + age);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * 保存数据
     */
    public void save(String username, String sex, int age) {
        //获取数据库连接
        Connection connection = getConnection();
        //保存
        String sql = "insert into t_user (username, sex, age) values (?, ?, ?)";
        //执行保存
        try {
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, username);
            preparedStatement.setString(2, sex);
            preparedStatement.setInt(3, age);
            //执行保存返回影响行数
            boolean execute = preparedStatement.execute();
            if (execute) {
                System.out.println("保存成功");
            } else {
                System.out.println("保存失败");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * 更新数据
     */
     public void update(String username, String sex, int age,int id) {
        //获取数据库连接
        Connection connection = getConnection();
        //更新
        String sql = "update t_user set sex = ?, age = ? ,username = ? where id=?";
        //执行更新
        try {
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, sex);
            preparedStatement.setInt(2, age);
            preparedStatement.setString(3, username);
            preparedStatement.setInt(4, id);
            //执行更新返回影响行数
            int rows = preparedStatement.executeUpdate();
            if (rows > 0) {
                System.out.println("更新成功");
            } else {
                System.out.println("更新失败");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * 删除数据
     */
     public void delete(int id) {
        //获取数据库连接
        Connection connection = getConnection();
        //删除
        String sql = "delete from t_user where id = ?";
        //执行删除
        try {
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, id);
            //执行删除返回影响行数
            int rows = preparedStatement.executeUpdate();
            if (rows > 0) {
                System.out.println("删除成功");
            } else {
                System.out.println("删除失败");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static void main(String[] args) {
        DbUtils dbUtils = new DbUtils();
        //保存
        //dbUtils.save("alice", "girl", 18);
        //更新
        //dbUtils.update("alice", "boy", 20, 20);
        //删除
        dbUtils.delete(20);
        //查询
        dbUtils.query("");
    }
}

























